﻿
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Collections;
    using System.Collections.Generic;

namespace FShop.DAL
{

    ///<summary>
    ///The Class Provides methods for common database table operations. 
    ///</summary>
    ///<remarks>
    ///Changes to this file may cause incorrect behavior and will be lost if  the code is regenerated.
    ///Update the <see cref="Advertise_DAL"/> class if you need to add or change some functionality
    ///</remarks>
    public class Advertise_DAL:BaseObject  {

        private const string SP_SELECT_BY_PRIMARY_KEY="usp_Advertise_SelectByPrimaryKey";
        private const string SP_SELECT_ROWS_BY_WHERE="usp_Advertise_SelectRowsByWhere";
        private const string SP_INSERT="usp_Advertise_Insert";
        private const string SP_UPDATE="usp_Advertise_Update";
        private const string SP_DELETE="usp_Advertise_DeleteByPrimaryKey";
        private const string SP_DELETE_WHERE="usp_Advertise_DeleteWhere";
        private const string SP_SELECT_GENERAL="usp_Advertise_SelectGeneral";
        private const string SP_SELECT_PAGING="usp_Advertise_SelectPaging";

        #region Constructor

        ///<summary>
        ///Initializes a new instance of the <see cref="AdvertiseDal"/> 
        ///class with the specified <see cref="BaseDAL"/>.
        ///</summary>
        public Advertise_DAL(): base()
        {

        }

        #endregion

        #region Public Methods
        ///<summary>
        ///Create Obj from ireader.
        ///</summary>

        private Advertise GetObjectFromReader(IDataReader reader) 
         {
             Advertise temp= new Advertise();
             temp.Adv_ID = (reader["Adv_ID"] is DBNull) ? int.MinValue :(System.Int32)reader["Adv_ID"] ;
             temp.Adv_Title = (reader["Adv_Title"] is DBNull) ? string.Empty :(System.String)reader["Adv_Title"] ;
             temp.Adv_Link = (reader["Adv_Link"] is DBNull) ? string.Empty :(System.String)reader["Adv_Link"] ;
             temp.Adv_Status = (reader["Adv_Status"] is DBNull) ? false :(System.Boolean)reader["Adv_Status"] ;
             temp.Adv_Position = (reader["Adv_Position"] is DBNull) ? int.MinValue :(System.Int32)reader["Adv_Position"] ;
             temp.Adv_Width = (reader["Adv_Width"] is DBNull) ? int.MinValue :(System.Int32)reader["Adv_Width"] ;
             temp.Adv_Height = (reader["Adv_Height"] is DBNull) ? int.MinValue :(System.Int32)reader["Adv_Height"] ;
             temp.Adv_CreateDate = (reader["Adv_CreateDate"] is DBNull) ? DateTime.MinValue :(System.DateTime)reader["Adv_CreateDate"] ;
             temp.Adv_EndDate = (reader["Adv_EndDate"] is DBNull) ? DateTime.MinValue :(System.DateTime)reader["Adv_EndDate"] ;
             temp.Adv_Desc = (reader["Adv_Desc"] is DBNull) ? string.Empty :(System.String)reader["Adv_Desc"] ;
             temp.Adv_Image = (reader["Adv_Image"] is DBNull) ? string.Empty :(System.String)reader["Adv_Image"] ;
             temp.Cate_ID = (reader["Cate_ID"] is DBNull) ? int.MinValue :(System.Int32)reader["Cate_ID"] ;
              return temp;
         }
        ///<summary>
        ///Get List Advertise by a primary key in object 
        ///</summary>
		public Advertise Get_Advertise_ByID(System.Int32 Adv_ID)
		{
		    SqlConnection conn = null;
		    try
		        {
		            using( conn = CreateConnection() )
		            {
                         SqlCommand cmd = new SqlCommand(SP_SELECT_BY_PRIMARY_KEY, conn);
                         cmd.CommandType = CommandType.StoredProcedure;
				        cmd.Parameters.Add(new SqlParameter("@Adv_ID",Adv_ID));
                         conn.Open();
                         Advertise temp = new Advertise();
                         using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
                         {
                             while (reader.Read())
                                 {
                                     temp=( GetObjectFromReader(reader) );
                                 }
                         }
                     conn.Close();
                     return temp;
		            }
		        }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		 }

        ///<summary>
        ///Get List Advertise and store in a list 
        ///</summary>
		public List<Advertise> GetList_Advertise()
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_ROWS_BY_WHERE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SqlWhere",""));
                conn.Open();
                List<Advertise> lst = new List<Advertise>();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection) )
                {
                    while (reader.Read())
                     {
                         lst.Add( GetObjectFromReader(reader) );
                     }
                }
                conn.Close();
                return lst;
		    }
		 }

		public void Delete_Advertise(Advertise temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_DELETE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@Adv_ID",temp.Adv_ID));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

		public void Delete_Advertise_Where(string whereSql)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_DELETE_WHERE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@Where",whereSql));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Get List Advertise as DataTable 
        ///</summary>
        /// <param name="whereSql">Where fields. example : 1=1 </param>
        /// <param name="orderBy"Order fields></param>
		public DataTable Get_AdvertiseAsTable(string whereSql,string orderBy)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_GENERAL, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@SqlWhere",whereSql));
			    cmd.Parameters.Add(new SqlParameter("@SqlOrderBy",orderBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
		       {
                 conn.Open();
                 DataTable tbl = new DataTable();
                 dataAdapter.Fill(tbl);
                 conn.Close();
                 return tbl;
		       }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Using for paging data 
        ///</summary>
        /// <param name="pageIndex">Number of pages</param>
        /// <param name="PageSize">Number of a rows per page</param>
        /// <param name="conditions">Where  fields</param>
        /// <param name="groupBy">Group by fields</param>
		public DataTable Get_AdvertiseAsPaging(int pageIndex,int pageSize,string sSortColumn,string sFields,string sWhere,string sGroupBy)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_SELECT_PAGING, conn);
                cmd.CommandType = CommandType.StoredProcedure;
			    cmd.Parameters.Add(new SqlParameter("@PageIndex",pageIndex));
			    cmd.Parameters.Add(new SqlParameter("@PageSize",pageSize));
			    cmd.Parameters.Add(new SqlParameter("@SortColumn",sSortColumn));
			    cmd.Parameters.Add(new SqlParameter("@Fields",sFields));
			    cmd.Parameters.Add(new SqlParameter("@Where",sWhere));
			    cmd.Parameters.Add(new SqlParameter("@Group",sGroupBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
		       {
                 conn.Open();
                 DataTable tbl = new DataTable();
                 dataAdapter.Fill(tbl);
                 conn.Close();
                 return tbl;
		       }
                catch
		        {
                     return null;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Insert new Advertise
        ///</summary>
		public void Insert_Advertise(Advertise temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_INSERT, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@Adv_Title",temp.Adv_Title));
				cmd.Parameters.Add(new SqlParameter("@Adv_Link",temp.Adv_Link));
				cmd.Parameters.Add(new SqlParameter("@Adv_Status",temp.Adv_Status));
				cmd.Parameters.Add(new SqlParameter("@Adv_Position",temp.Adv_Position));
				cmd.Parameters.Add(new SqlParameter("@Adv_Width",temp.Adv_Width));
				cmd.Parameters.Add(new SqlParameter("@Adv_Height",temp.Adv_Height));
				cmd.Parameters.Add(new SqlParameter("@Adv_CreateDate",temp.Adv_CreateDate));
				cmd.Parameters.Add(new SqlParameter("@Adv_EndDate",temp.Adv_EndDate));
				cmd.Parameters.Add(new SqlParameter("@Adv_Desc",temp.Adv_Desc));
				cmd.Parameters.Add(new SqlParameter("@Adv_Image",temp.Adv_Image));
				cmd.Parameters.Add(new SqlParameter("@Cate_ID",temp.Cate_ID));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

        ///<summary>
        ///Update a Advertise
        ///</summary>
		public void Update_Advertise(Advertise temp)
		{
		    using( SqlConnection conn = CreateConnection() )
		    {
                SqlCommand cmd = new SqlCommand(SP_UPDATE, conn);
                cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new SqlParameter("@Adv_ID",temp.Adv_ID));
				cmd.Parameters.Add(new SqlParameter("@Adv_Title",temp.Adv_Title));
				cmd.Parameters.Add(new SqlParameter("@Adv_Link",temp.Adv_Link));
				cmd.Parameters.Add(new SqlParameter("@Adv_Status",temp.Adv_Status));
				cmd.Parameters.Add(new SqlParameter("@Adv_Position",temp.Adv_Position));
				cmd.Parameters.Add(new SqlParameter("@Adv_Width",temp.Adv_Width));
				cmd.Parameters.Add(new SqlParameter("@Adv_Height",temp.Adv_Height));
				cmd.Parameters.Add(new SqlParameter("@Adv_CreateDate",temp.Adv_CreateDate));
				cmd.Parameters.Add(new SqlParameter("@Adv_EndDate",temp.Adv_EndDate));
				cmd.Parameters.Add(new SqlParameter("@Adv_Desc",temp.Adv_Desc));
				cmd.Parameters.Add(new SqlParameter("@Adv_Image",temp.Adv_Image));
				cmd.Parameters.Add(new SqlParameter("@Cate_ID",temp.Cate_ID));
                try
		        {
                 conn.Open();
                 cmd.ExecuteNonQuery();
                 conn.Close();
		        }
                catch(SqlException ex)
		        {
		            throw ex;
		        }
                finally
		        {
		            if(conn.State==ConnectionState.Open)
                         conn.Close();
		        }
		    }
		 }

#endregion
 }
}
